/*============================================================================
  File:     PersistIndexStats.sql

  Summary:  Be careful... don't just drop all unused indexes! Please use
			this script with caution. If your persisted information is not
			comprehensive then you might be dropping useful indexes - just
			not indexes used since you started persisting the data!!!
           
  Date:     February 2011

  SQL Server Version: 2005/2008
------------------------------------------------------------------------------
  Written by Kimberly L. Tripp, SYSolutions, Inc.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

-- generically look at all indexes - and persist this data so that you can analyze over time

USE master;
GO

IF DATABASEPROPERTYEX (N'PerformanceDW', N'Version') IS NULL
BEGIN
	CREATE DATABASE PerformanceDW ON PRIMARY
	(NAME = N'PerformanceDW',
		FILENAME = N'C:\MSSQL\Data\PerformanceDW.mdf',
		SIZE = 512MB, FILEGROWTH = 256MB
	) LOG ON (NAME = N'PerformanceDW_log',
		FILENAME = N'C:\MSSQL\Data\PerformanceDW.ldf',
		SIZE = 64MB, FILEGROWTH = 32MB
	);

	ALTER DATABASE PerformanceDW SET RECOVERY SIMPLE;
END
GO

USE PerformanceDW;
GO

-- create the table to persist the index usage stats
CREATE TABLE dbo.IndexUsageStats
(
	row_num BIGINT NOT NULL,
	[capture_date] DATETIME NOT NULL,
	[sqlserver_startup_time] DATETIME NOT NULL,
	[database_create_date] DATETIME NOT NULL,
	[database_name] SYSNAME NOT NULL,
	[schema_name] SYSNAME NOT NULL,
	[table_name] SYSNAME NOT NULL,
	[index_name] SYSNAME NOT NULL,
	[database_id] SMALLINT NOT NULL,
	[object_id] INT NOT NULL,
	[index_id] INT NOT NULL,
	[user_seeks] BIGINT NOT NULL,
	[user_scans] BIGINT NOT NULL,
	[user_lookups] BIGINT NOT NULL,
	[user_updates] BIGINT NOT NULL,
	[last_user_seek] DATETIME NULL,
	[last_user_scan] DATETIME NULL,
	[last_user_lookup] DATETIME NULL,
	[last_user_update] DATETIME NULL
) ON [PRIMARY];
GO

-- create a unique clustered index on capture_date and row_num
CREATE UNIQUE CLUSTERED INDEX CI_RunTime ON dbo.IndexUsageStats
	(capture_date, row_num) ON [PRIMARY];
GO

-- create a unique nonclustered index to uniquely identify each index
CREATE UNIQUE NONCLUSTERED INDEX UK_IndexUsageStat ON dbo.IndexUsageStats
(
	[capture_date] ASC,
	[database_name] ASC,
	[schema_name] ASC,
	[table_name] ASC,
	[index_name] ASC
) ON [PRIMARY];
GO

USE <database_name>;
GO

-- periodically persist index usage stats
INSERT INTO PerformanceDW.dbo.IndexUsageStats
SELECT
	ROW_NUMBER() OVER (ORDER BY ius.database_id ASC, ius.object_id ASC, ius.index_id ASC) AS row_num,
	GETDATE() AS capture_date,
	osi.sqlserver_start_time,
	db.create_date AS database_create_date,
	DB_NAME(ius.database_id) AS database_name, 
	OBJECT_SCHEMA_NAME(ius.object_id, ius.database_id) AS 'schema_name',
	OBJECT_NAME(ius.object_id, ius.database_id) AS 'table_name',
	i.name AS index_name,
	ius.database_id,
	ius.object_id,
	ius.index_id,
	ius.user_seeks,
	ius.user_scans,
	ius.user_lookups,
	ius.user_updates,
	ius.last_user_seek,
	ius.last_user_scan,
	ius.last_user_lookup,
	ius.last_user_update 
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.indexes i ON ius.index_id = i.index_id
	AND ius.object_id = i.object_id
INNER JOIN sys.databases db ON ius.database_id = db.database_id
CROSS APPLY (SELECT sqlserver_start_time FROM sys.dm_os_sys_info) AS osi
WHERE i.object_id > 100 AND ius.database_id > 4 AND ius.database_id != DB_ID('PerformanceDW');
GO

-- query the index stats
;WITH IndexStats AS (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY sqlserver_startup_time, database_create_date, database_name, schema_name, table_name, index_name, object_id ORDER BY capture_date DESC) AS RowNum
    FROM PerformanceDW.dbo.IndexUsageStats
)
SELECT *
FROM IndexStats WHERE RowNum = 1
-- AND database_name = '<database_name>' AND table_name = '<table_name>' AND index_name = '<index_name>'
ORDER BY sqlserver_startup_time DESC, database_create_date DESC, database_name ASC, schema_name ASC, table_name ASC, index_name ASC;

-- verify the indexes NOT used for a particular database
/*
--------------------------------- BEWARE!!! ---------------------------------
these are the indexes NOT used since you started gathering 
"usage stats." If you have NOT gathered stats over a true "business cycle" 
then you may NOT have accurate stats (for example for month end processing) 
and therefore you might actually drop an index that's very critical for a
report or other and that might have a profound affect on overall 
performance (during month-end)...
-----------------------------------------------------------------------------
*/

USE <database_name>;
GO

SELECT
	OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
	OBJECT_NAME(i.object_id) AS 'table_name',
	i.index_id,
	i.name AS 'index_name',
	N'ALTER INDEX ' 
		+ QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id), N']')
		+ N'.'
		+ QUOTENAME(OBJECT_NAME(i.object_id), N']') 
		+ N'.' 
		+ QUOTENAME(i.name, N']') + ' DISABLE;' AS 'disable_index_statement'
FROM sys.indexes AS i 
WHERE 
	(
	 -- this first statement looks for indexes that aren't listed AT ALL in the usage stats
		i.index_id NOT IN (SELECT pius.index_id 
            FROM PerformanceDW.dbo.IndexUsageStats AS pius
            WHERE pius.object_id = i.object_id AND 
                  i.index_id = pius.index_id AND 
                  database_id = DB_ID()) 
    OR
	 -- this second statement looks for indexes that are rarely being used for seeks/scans but have updates
		i.index_id IN (SELECT pius.index_id 
            FROM PerformanceDW.dbo.IndexUsageStats AS pius 
            WHERE pius.object_id = i.object_id AND 
                  i.index_id = pius.index_id AND 
                  database_id = db_id()
                  AND pius.user_seeks < 5		-- need to adjust this (possibly, start with 5)
                  AND pius.user_scans < 5		-- need to adjust this (possibly, start with 5)
                  AND pius.user_lookups < 5		-- need to adjust this (possibly, start with 5)
                  AND pius.user_updates > 100	-- need to adjust this (possibly, start with 100)
				  AND pius.last_user_seek < DATEADD(day, -60, GETDATE()))	-- need to adjust this (possibly, start with 60 days)
   	)
AND i.object_id > 100 
	AND i.index_id > 0 -- don't drop heaps (it would fail regardless because you can't drop a heap with DROP INDEX...)
	AND i.is_primary_key = 0
	AND i.is_unique_constraint = 0
	AND i.is_unique = 0
	AND i.is_hypothetical = 0
	AND OBJECTPROPERTY(i.object_id,'IsUserTable') = 1
ORDER BY table_name ASC, index_name ASC;

-- cleanup
/*
USE master;
GO

IF DATABASEPROPERTYEX (N'PerformanceDW', N'Version') > 0
BEGIN
	ALTER DATABASE PerformanceDW SET SINGLE_USER
		WITH ROLLBACK IMMEDIATE;
	DROP DATABASE PerformanceDW;
END
*/